IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[Ward]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
   DROP TABLE [dbo].[Ward]
GO

/*----------------------------------------------------------------------
'Created By : Preeti
'Created On : 10/18/2008
'SP Name    : dbo.SP_SetWard
'Used In    : 
'------------------------------------------------------------------------
'Modification Log  
'------------------------------------------------------------------------
'Created By        Modified By         Remarks                             
'-----------       ------------        ----------------------------------
'                                                                          
'----------------------------------------------------------------------*/

CREATE TABLE [dbo].[Ward] (
   WardId   Varchar(30),
   [WardName]     Varchar(50),
   [Location]     Varchar(50),
   [WardType]     Varchar(50),
   [NumberOfBeds]     Varchar(50),
   [Remarks]     Varchar(50)
)
GO

GRANT SELECT,INSERT,DELETE,UPDATE ON dbo.Ward TO hms_usr
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id =
object_id(N'dbo.SP_GetWard') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
   DROP PROCEDURE dbo.SP_GetWard
GO


/*----------------------------------------------------------------------
'Created By : Preeti
'Created On : 10/18/2008
'SP Name    : dbo.SP_GetWard
'Used In    : 
'------------------------------------------------------------------------
'Modification Log  
'------------------------------------------------------------------------
'Created By        Modified By         Remarks                             
'-----------       ------------        ----------------------------------
'                                                                          
'----------------------------------------------------------------------*/

CREATE PROCEDURE dbo.SP_GetWard
@vchWardId VARCHAR(30)
AS
   SELECT
       WardId,
       WardName,
       Location,
       WardType,
       NumberOfBeds,
       Remarks
   FROM Ward
   WHERE WardId=@vchWardId
GO

GRANT EXEC ON dbo.SP_GetWard TO hms_usr
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id =
object_id(N'dbo.SP_SetWard') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
   DROP PROCEDURE dbo.SP_SetWard
GO


/*----------------------------------------------------------------------
'Created By : Preeti
'Created On : 10/18/2008
'SP Name    : dbo.SP_SetWard
'Used In    : 
'------------------------------------------------------------------------
'Modification Log  
'------------------------------------------------------------------------
'Created By        Modified By         Remarks                             
'-----------       ------------        ----------------------------------
'                                                                          
'----------------------------------------------------------------------*/

CREATE PROCEDURE dbo.SP_SetWard
@vchWardId Varchar(30),
@vchWardName    Varchar(50),
@vchLocation    Varchar(50),
@vchWardType    Varchar(50),
@vchNumberOfBeds    Varchar(50),
@vchRemarks    Varchar(50)
AS
DECLARE  @iRetValue     integer
   IF NOT EXISTS(SELECT     WardId
   FROM Ward
   WHERE   (WardId = @vchWardId)
   )

   BEGIN
   INSERT INTO Ward(
       WardId,
       WardName,
       Location,
       WardType,
       NumberOfBeds,
       Remarks
  )
   VALUES (
      @vchWardId,
       @vchWardName,
       @vchLocation,
       @vchWardType,
       @vchNumberOfBeds,
       @vchRemarks
   )
   END
ELSE  IF EXISTS(SELECT     WardId
   FROM Ward
   WHERE   (WardId = @vchWardId)
   )

   BEGIN
   UPDATE Ward
   SET
       WardName = @vchWardName,
       Location = @vchLocation,
       WardType = @vchWardType,
       NumberOfBeds = @vchNumberOfBeds,
       Remarks = @vchRemarks
  WHERE (WardId = @vchWardId)
   END

IF (@@ERROR <> 0)
   BEGIN
       declare @SPErrMsg varchar(800)
       SET @SPErrMsg = 'Error: Error in Stored procedure dbo.SP_SetWard for Loan# : ' + CAST (@vchWardId as varchar)
       RAISERROR (@SPErrMsg,16,1)
       Select @iRetValue = -1
   END
ELSE
   BEGIN
       Select @iRetValue = 0
   END
   
   RETURN @iRetValue
GO

GRANT EXEC ON dbo.SP_SetWard TO hms_usr
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id =
object_id(N'dbo.SP_DeleteWard') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
   DROP PROCEDURE dbo.SP_DeleteWard
GO


/*----------------------------------------------------------------------
'Created By : Preeti
'Created On : 10/18/2008
'SP Name    : dbo.SP_DeleteWard
'Used In    : 
'------------------------------------------------------------------------
'Modification Log  
'------------------------------------------------------------------------
'Created By        Modified By         Remarks                             
'-----------       ------------        ----------------------------------
'                                                                          
'----------------------------------------------------------------------*/

CREATE PROCEDURE dbo.SP_DeleteWard
@vchWardId VARCHAR(30)
AS
   DELETE FROM Ward  WHERE   (WardId = @vchWardId)

GRANT EXEC ON dbo.SP_DeleteWard TO hms_usr
GO

